package com.tom;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mysql.jdbc.Driver;

public class MemberDAO 
{
    public static Connection conn = null;

    public static Connection getConnection() {
            if (conn == null) {
                    try {
                            DriverManager.registerDriver(new Driver());
                            conn = DriverManager.getConnection(
                                            "jdbc:mysql://j.snpy.org/j102", "jstu2", "abc123");
                    } catch (SQLException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                    }
            }
            return conn;

    }

    public List<Member> getAll() 
    {
            List<Member> members = new ArrayList<Member>();
            Statement stmt;
            ResultSet rs;
            
            try {
                    stmt = getConnection().createStatement();
                    rs = stmt.executeQuery("select * from users");
                    while (rs.next()) {
                            String id = rs.getString("id");
                            String nickname = rs.getString("nickname");
                            String password = rs.getString("password");
                            String email = rs.getString("email");
                            Member member = new Member(id, nickname, password, email, password);
                            members.add(member);
                    }
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            finally
            {
            	if(conn != null)
            	{
            		stmt = null;
            		conn = null;
            	}
            }
            
            return members;
    }
    
    public int add(Member m){
        String sql = "insert into users(id,nickname,password,email) values (?,?,?,?)";
        int rowCount = 0;
        try {
                PreparedStatement pstmt = getConnection().prepareStatement(sql);
                pstmt.setString(1, m.getId());
                pstmt.setString(2, m.getNickname());
                pstmt.setString(3, m.getPassword());
                pstmt.setString(4, m.getEmail());
                rowCount = pstmt.executeUpdate();
                System.out.println(rowCount);
        } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();               
         
        }
        return rowCount;
}

}
